/******************************************************************************
 * Product: Adempiere ERP & CRM Smart Business Solution                       *
 * Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved.                *
 * This program is free software; you can redistribute it and/or modify it    *
 * under the terms version 2 of the GNU General Public License as published   *
 * by the Free Software Foundation. This program is distributed in the hope   *
 * that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.           *
 * See the GNU General Public License for more details.                       *
 * You should have received a copy of the GNU General Public License along    *
 * with this program; if not, write to the Free Software Foundation, Inc.,    *
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.                     *
 * For the text or an alternative of this public license, you may reach us    *
 * ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA        *
 * or via info@compiere.org or http://www.compiere.org/license.html           *
 *****************************************************************************/
package org.compiere.report;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MElementValue;
import org.compiere.model.MOrg;
import org.compiere.process.BalanceSheets;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * Analyze Account
 * 
 * @author Duman Zhunissov
 * 
 */
public class AnalyzeAcct extends SvrProcess {

	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);

	private String getSQlresult = null;

	/** AcctSchame Parameter */
	private int p_C_AcctSchema_ID = 0;
	/** Period Parameter */
	private Timestamp p_DateAcct_From = null;
	private Timestamp p_DateAcct_To = null;
	/** Org Parameter */
	private int p_AD_Org_ID = 0;
	/** Account Parameter */
	private int p_Account_ID = 0;
	/** BPartner Parameter */
	private int p_C_BPartner_ID = 0;
	/** Product Parameter */
	private int p_M_Product_ID = 0;
	/** Project Parameter */
	private int p_C_Project_ID = 0;
	/** Activity Parameter */
	private int p_C_Activity_ID = 0;
	/** SalesRegion Parameter */
	private int p_C_SalesRegion_ID = 0;
	/** Campaign Parameter */
	private int p_C_Campaign_ID = 0;
	/** Posting Type */
	private String p_PostingType = "A";
	/** Hierarchy */
	private int p_PA_Hierarchy_ID = 0;

	private int p_AD_OrgTrx_ID = 0;
	private int p_C_LocFrom_ID = 0;
	private int p_C_LocTo_ID = 0;
	private int p_User1_ID = 0;
	private int p_User2_ID = 0;

	/** Parameter Where Clause */
	private StringBuffer m_parameterWhere = new StringBuffer();
	/** Account */
	private MElementValue m_acct = null;

	/** Start Time */
	private long m_start = System.currentTimeMillis();

	/**
	 * Prepare - e.g., get Parameters.
	 */
	protected void prepare() {
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		// Parameter
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("C_AcctSchema_ID"))
				p_C_AcctSchema_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("DateAcct")) {
				p_DateAcct_From = (Timestamp) para[i].getParameter();
				p_DateAcct_To = (Timestamp) para[i].getParameter_To();
			} else if (name.equals("PA_Hierarchy_ID"))
				p_PA_Hierarchy_ID = para[i].getParameterAsInt();
			else if (name.equals("AD_Org_ID"))
				p_AD_Org_ID = ((BigDecimal) para[i].getParameter()).intValue();
			else if (name.equals("Account_ID"))
				p_Account_ID = ((BigDecimal) para[i].getParameter()).intValue();
			else if (name.equals("C_BPartner_ID"))
				p_C_BPartner_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("M_Product_ID"))
				p_M_Product_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("C_Project_ID"))
				p_C_Project_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("C_Activity_ID"))
				p_C_Activity_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("C_SalesRegion_ID"))
				p_C_SalesRegion_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("C_Campaign_ID"))
				p_C_Campaign_ID = ((BigDecimal) para[i].getParameter())
						.intValue();
			else if (name.equals("PostingType"))
				p_PostingType = (String) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		if (p_DateAcct_To == null)
			p_DateAcct_To = p_DateAcct_From;

	} // prepare

	/**************************************************************************
	 * Perform process.
	 * 
	 * @return Message to be translated
	 * @throws IOException
	 * @throws WriteException
	 */
	protected String doIt() throws WriteException, IOException {
		return toExcel();
	} // doIt

	protected String toExcel() throws IOException, WriteException {
		// Types of cells for
		Label label;
		Number number;
		String AccountValue = null;
		// There are checks for a specified pattern
		String templateName = "FI_AnalyzeAccount_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);
		//
		// // We define a path to generate
		// String fileExtension =
		// entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		// StringBuffer fullPath = new StringBuffer(path);
		// fullPath.append("Temporary Balance Sheets");
		// fullPath.append(fileExtension);
		// //
		String additionalTitle = new Timestamp(System.currentTimeMillis()).toString().substring(0, 10)+"_"
				+new Timestamp(System.currentTimeMillis()).toString().substring(11, 13)+"-"
		+new Timestamp(System.currentTimeMillis()).toString().substring(14, 16)+"-"
				+new Timestamp(System.currentTimeMillis()).toString().substring(17, 19);
		PreparedStatement pstmt1 = null;
		ResultSet rs1 = null;
		StringBuilder sql1 = new StringBuilder("select value from c_elementvalue where c_elementvalue_id = " + p_Account_ID);
		try{
			pstmt1 = DB.prepareStatement(sql1.toString(), get_TrxName());
			rs1 = pstmt1.executeQuery();
			while(rs1.next()){
				additionalTitle="-"+rs1.getString(1)+"_от_"+additionalTitle;
			}
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql1.toString(), e);
		}
		finally{
			DB.close(rs1, pstmt1);
			rs1 = null; pstmt1 = null;
		}
		
		File templateCopy = new File(Util.localFilePath
				+ "Анализ счета"+additionalTitle+".xls");
		new Timestamp(System.currentTimeMillis()).toString();
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}
		

		
		// ----------------------------
		List<line> list = getDatas();
		if (getSQlresult != null)
			return getSQlresult;

		if (list.size() == 0)
			return Msg.translate(m_ctx,
					"По данным параметрам данные отсутствуют");
// Добавляем название организации. Ибраев Е.А. 24.12.2012
		WritableFont arial10ptBold1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
		WritableCellFormat captionStyle1 = new WritableCellFormat(arial10ptBold1);
		MOrg org = new MOrg(m_ctx, p_AD_Org_ID, null);
		String orgname = "";
		if (org != null) {
			orgname = org.getName();
		}
		label = new Label(0, 0, orgname, captionStyle1);
		sheet.addCell(label);
		
//		Анализ счета 1210 с 01.01.2012 по 31.01.2012
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder("select value from c_elementvalue where c_elementvalue_id = " + p_Account_ID);
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
				WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold);
				label = new Label(0, 1, ("Анализ счета: " + rs.getString(1) + " за период с " 
						+ new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_From) 
						+ " по " 
						+ new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct_To) 
						), captionStyle);
				sheet.addCell(label);
				//----by Aslanbek
				AccountValue = rs.getString(1);
				//----
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_AnalyzeAccount", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		WritableFont arial8ptNOBold = new WritableFont(WritableFont.ARIAL, 8,
				WritableFont.NO_BOLD);
		WritableCellFormat vsFloat = null;
		vsFloat = new WritableCellFormat(arial8ptNOBold,
				NumberFormats.THOUSANDS_FLOAT);
		vsFloat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setAlignment(Alignment.RIGHT);
		vsFloat.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsFloat.setWrap(true);

		WritableCellFormat valuestyle = new WritableCellFormat(arial8ptNOBold);
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle.setAlignment(Alignment.LEFT);
		valuestyle.setVerticalAlignment(VerticalAlignment.TOP);
		valuestyle.setWrap(true);
		//-----------
		WritableCellFormat valuestyle1 = new WritableCellFormat(arial8ptNOBold);
		valuestyle1.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle1.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle1.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle1.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.getInternalColour(47));
		valuestyle1.setAlignment(Alignment.LEFT);
		valuestyle1.setVerticalAlignment(VerticalAlignment.TOP);
		valuestyle1.setWrap(true);
		valuestyle1.setBackground(Colour.getInternalColour(28));
		//-----------
		WritableCellFormat beginSaldoStyle = new WritableCellFormat(arial8ptNOBold,NumberFormats.THOUSANDS_FLOAT);
		beginSaldoStyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		beginSaldoStyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		beginSaldoStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.getInternalColour(47));
		beginSaldoStyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.getInternalColour(47));
		beginSaldoStyle.setAlignment(Alignment.RIGHT);
		beginSaldoStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		beginSaldoStyle.setWrap(true);
		beginSaldoStyle.setBackground(Colour.getInternalColour(28));
		//--------------
		BigDecimal summCr = new BigDecimal(0);
		BigDecimal summDr = new BigDecimal(0);
		//BigDecimal summCr1 = new BigDecimal(0);
		//BigDecimal summDr1 = new BigDecimal(0);
		//----- add by Aslanbek
		label = new Label(0, 3, AccountValue,valuestyle1);
		sheet.addCell(label);
		List<line> beginsaldo = getBeginSaldo();
		line bl = null;	
		if(beginsaldo.size() != 0){
			/// в БУ
			bl = beginsaldo.get(0);
			number = new Number(2, 3, bl.dr.doubleValue(),beginSaldoStyle);
			sheet.addCell(number);
			number = new Number(3, 3, bl.cr.doubleValue(),beginSaldoStyle);
			sheet.addCell(number);
			///в Вал
			//bl = beginsaldo.get(1);
			//number = new Number(3, 4, bl.dr1.doubleValue(),beginSaldoStyle);
			//sheet.addCell(number);
			//number = new Number(4, 4, bl.cr1.doubleValue(),beginSaldoStyle);
			//sheet.addCell(number);
		}
		else {
			beginsaldo.add(new line());
			///в БУ
			bl = beginsaldo.get(0);
			bl.setDr(new BigDecimal(0));
			bl.setCr(new BigDecimal(0));
			number = new Number(2, 3, bl.dr.doubleValue(),beginSaldoStyle);
			sheet.addCell(number);
			number = new Number(3, 3, bl.cr.doubleValue(),beginSaldoStyle);
			sheet.addCell(number);
			//beginsaldo.add(new line());
			///в Вал
			//bl = beginsaldo.get(1);
			//bl.setDr(new BigDecimal(0));
			//bl.setCr(new BigDecimal(0));
			//number = new Number(3, 4, bl.dr1.doubleValue(),beginSaldoStyle);
			//sheet.addCell(number);
			//number = new Number(4, 4, bl.cr1.doubleValue(),beginSaldoStyle);
			//sheet.addCell(number);
		}
		//-----
		int row = 4;
		for (line l : list) {
			//if (row > 5)
			//	sheet.insertRow(row);
			//в БУ
			//label = new Label(2, row, "БУ", valuestyle);
			//sheet.addCell(label);
			sheet.insertRow(row);
			number = new Number(2, row, l.dr.doubleValue(), vsFloat);
			sheet.addCell(number);
			number = new Number(3, row, l.cr.doubleValue(), vsFloat);
			sheet.addCell(number);

			//в Вал
			//label = new Label(2, row, "Вал.", valuestyle);
			//sheet.addCell(label);
			//number = new Number(3, row, l.dr1.doubleValue(), vsFloat);
			//sheet.addCell(number);
			//number = new Number(4, row, l.cr1.doubleValue(), vsFloat);
			//sheet.addCell(number);

			//sheet.mergeCells(0, row-1, 0, row);
			//sheet.mergeCells(1, row-1, 1, row);
			label = new Label(1, row, l.AccValue, valuestyle);
			sheet.addCell(label);			
			//Сумма в БУ
			summDr = summDr.add(l.dr);
			summCr = summCr.add(l.cr);
			//Сумма в Вал
			//summDr1 = summDr1.add(l.dr1);
			//summCr1 = summCr1.add(l.cr1);
			row++;
		}
		
		
		vsFloat = new WritableCellFormat(arial8ptNOBold,
				NumberFormats.THOUSANDS_FLOAT);
		vsFloat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.getInternalColour(47));
		vsFloat.setAlignment(Alignment.RIGHT);
		vsFloat.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsFloat.setWrap(true);
		vsFloat.setBackground(Colour.getInternalColour(28));
		//-----add by Aslanbek
		label = new Label(0, row, AccountValue,valuestyle1);
		sheet.addCell(label);
		//
		number = new Number(2, row, summDr.doubleValue(), vsFloat);
		sheet.addCell(number);
		number = new Number(3, row, summCr.doubleValue(), vsFloat);
		sheet.addCell(number);
		//row++;
		//number = new Number(3, row, summDr1.doubleValue(), vsFloat);
		//sheet.addCell(number);
		//number = new Number(4, row, summCr1.doubleValue(), vsFloat);
		//sheet.addCell(number);
		row++;
		//----------add by Aslanbek
		label = new Label(0, row, AccountValue,valuestyle1);
		sheet.addCell(label);
		//в БУ
		if((summDr.doubleValue() + bl.dr.doubleValue()) <= (summCr.doubleValue() + bl.cr.doubleValue())){
			number = new Number(4, row,(summCr.doubleValue() + bl.cr.doubleValue()) - (summDr.doubleValue() + bl.dr.doubleValue()), vsFloat);
			sheet.addCell(number);
		}
		else{
			number = new Number(3, row,(summDr.doubleValue() + bl.dr.doubleValue()) - (summCr.doubleValue() + bl.cr.doubleValue()) , vsFloat);
			sheet.addCell(number);
		}
		//row++;
		//в Вал
		/*if((summDr1.doubleValue() + bl.dr1.doubleValue()) <= (summCr1.doubleValue() + bl.cr1.doubleValue())){
			number = new Number(4, row,(summCr1.doubleValue() + bl.cr1.doubleValue()) - (summDr1.doubleValue() + bl.dr1.doubleValue()), vsFloat);
			sheet.addCell(number);
		}
		else{
			number = new Number(3, row,(summDr1.doubleValue() + bl.dr1.doubleValue()) - (summCr1.doubleValue() + bl.cr1.doubleValue()) , vsFloat);
			sheet.addCell(number);
		}*/
//		get color
//		for(int i=1; i<100;i++){
//			WritableCellFormat valuestyle22 = new WritableCellFormat();
//			valuestyle22.setBackground(Colour.getInternalColour(i));
//			label = new Label(10, i, Integer.valueOf(i).toString(), valuestyle22);
//			sheet.addCell(label);
//	}
		// ----------------------------
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();
		//
		try {
			String fileName = Util.localFilePath + "Анализ счета"+additionalTitle+".xls";// fullPath.toString();
			String command = "excel \"" + fileName + "\"";
			Runtime.getRuntime().exec("cmd /c start " + command);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return Msg.translate(m_ctx, "Success");

	}

	private List<line> getDatas() {
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuilder sql = getSQl(p_DateAcct_From, p_DateAcct_To, p_Account_ID);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				line l = new line();
				l.AccValue = rs.getString(1);
				l.dr = rs.getBigDecimal(2);
				l.cr = rs.getBigDecimal(3);
				//l.dr1 = rs.getBigDecimal(4);
				//l.cr1 = rs.getBigDecimal(5);
				//l.curr = rs.getString(4);

				list.add(l);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_AnalyzeAccount", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list;
	}
	

	private StringBuilder getSQl(Timestamp tsStartDate, Timestamp tsEndDate,
			Integer p_AccValue) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT "
				+ " ( "
				+ " 	SELECT cein.value FROM Fact_Acct fain inner join c_elementvalue cein on cein.c_elementvalue_id = fain.account_id "
				+ " 	WHERE fain.AD_Table_ID = fa.AD_Table_ID  AND fain.Record_ID = fa.Record_ID AND fain.Counter = fa.Reference  "
				+ " ) accValue "
				+ " ,sum(AmtAcctDr) dr "
				+ " ,sum(AmtAcctCr) cr "
//				+ " ,sum(AmtSourceDr) dr1 "
//				+ " ,sum(AmtSourceCr) cr1 "
//				+ " ,(SELECT iso_code FROM C_Currency WHERE c_currency_id = fa.c_currency_id) curr "
				+ " FROM Fact_Acct fa  " + " WHERE fa.account_id = "
				+ Integer.valueOf(p_AccValue).toString()
				+ " AND DateAcct BETWEEN '" + tsStartDate + "' and '"
				+ tsEndDate + "' " + "and fa.postingtype = 'A' "
				+ " group by accValue " + " order BY accValue ");

		return sql;
	}

	//-----------------------------------by Aslanbek
	private List<line> getBeginSaldo(){
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		StringBuilder sql = getBeginSQl(p_DateAcct_From, p_DateAcct_To, p_Account_ID);
		
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				line strSaldo = new line();
				strSaldo.AccValue = rs.getString(1);
				strSaldo.dr = rs.getBigDecimal(2);
				strSaldo.cr = rs.getBigDecimal(3);
				//strSaldo.dr1 = rs.getBigDecimal(4);
				//strSaldo.cr1 = rs.getBigDecimal(5);
				strSaldo.curr = rs.getString(4);

				list.add(strSaldo);
			}

			
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_AnalyzeAccount", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		
		return list;		//.toArray(new line[list.size()]);
	}
	private StringBuilder getBeginSQl(Timestamp tsStartDate, Timestamp tsEndDate,
			Integer p_AccValue) {
		StringBuilder sql = new StringBuilder();
		sql.append( "SELECT " 
				+ " c.value accValue" 
				+ ",COALESCE(adempiere.func1(sum(AmtAcctDr),sum(AmtAcctCr),'dr'),0) dr "
				+ ",COALESCE(adempiere.func1(sum(AmtAcctDr),sum(AmtAcctCr),'cr'),0) cr "
//				+ ",COALESCE(adempiere.func1(sum(AmtSourceDr),sum(AmtSourceCr),'dr'),0) dr1 "
//				+ ",COALESCE(adempiere.func1(sum(AmtSourceDr),sum(AmtSourceCr),'cr'),0) cr1 "
				+ " ,(SELECT iso_code FROM C_Currency WHERE c_currency_id = fa.c_currency_id) curr "
				+ " FROM Fact_Acct fa,C_Elementvalue c  " + " WHERE fa.account_id = "
				+ Integer.valueOf(p_AccValue).toString()
				+ " AND fa.account_id = c.C_Elementvalue_ID"
				+ " AND DateAcct < '" + tsStartDate + "'"
				+ " and fa.postingtype = 'A' "
				+ " group by accValue, curr " + " order BY accValue ");

		return sql;
	}
	//-----------------------------------by Aslanbek
	class line {
		line(String accValue,BigDecimal dr, BigDecimal cr,BigDecimal dr1, BigDecimal cr1, String curr){
			AccValue = accValue;
			dr = dr;
			cr = cr;
			dr1 = dr1;
			cr1 = cr1;
			curr = curr;
		}
		
		line(){
			AccValue = "";
			dr = new BigDecimal(0);
			cr = new BigDecimal(0);
//			dr1 = new BigDecimal(0);
//			cr1 = new BigDecimal(0);
			curr = "";
		}
		String AccValue = null;
		BigDecimal dr = null;
		BigDecimal cr = null;
//		BigDecimal dr1 = null;
//		BigDecimal cr1 = null;
		String curr = null;
		//---modify by Aslanbek
		public BigDecimal getDr() {
			return dr;
		}
		public void setDr(BigDecimal dr) {
			this.dr = dr;
		}
		public BigDecimal getCr() {
			return cr;
		}
		public void setCr(BigDecimal cr) {
			this.cr = cr;
		}
		public String getAccValue() {
			return AccValue;
		}
		public void setAccValue(String accValue) {
			AccValue = accValue;
		}
		public String getCurr() {
			return curr;
		}
		public void setCurr(String curr) {
			this.curr = curr;
		}
		
		//---
	}
} // Analyze Account
